package io.github.syske.springbootjdbcmybatisdemo.common;

import org.springframework.stereotype.Component;

import java.sql.*;

@Component
public class GetOPCodeUtil {
    public static final String url = "jdbc:oracle:thin:@127.0.0.1:1521/MWMS";
    public static final String user = "std_mwms_whqr";
    public static final String password = "hsbk_feng_whqr";

    public  String getOPCode(){
        String opCode = null;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "{ ? = call F_Get_PK(?, ?,?) }";
            try (CallableStatement stmt = conn.prepareCall(sql)) {
                stmt.registerOutParameter(1, Types.VARCHAR); // 输出参数类型为INTEGER
                stmt.setString(2, "op_op_code"); // 第一个输入参数
                stmt.setString(3, "1"); // 第二个输入参数
                stmt.setInt(4, 1); // 第二个输入参数

                stmt.execute();

                String result = stmt.getString(1);
                opCode = result;
                System.out.println("The result is: " + result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return opCode;
    }

    public  String callSelProcedure(String opCode){
        String as_Rtn = null;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "{ call sp_Xml_Sel_L(?, ?) }"; // 调用存储过程的SQL语句
            try (CallableStatement stmt = conn.prepareCall(sql)) {
                stmt.setString(1, opCode); // 设置输入参数的值
                stmt.registerOutParameter(2, Types.VARCHAR); // 注册输出参数的类型

                stmt.execute(); // 执行存储过程

                String outputParam = stmt.getString(2); // 获取输出参数的值
                as_Rtn = outputParam;
                System.out.println("Output parameter: " + outputParam);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return as_Rtn;
    }

    public  String callTempOpProcedure(String opCode){
        String as_Rtn = null;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "{ call SP_XML_TEMP_OP(?, ?) }"; // 调用存储过程的SQL语句
            try (CallableStatement stmt = conn.prepareCall(sql)) {
                stmt.setString(1, opCode); // 设置输入参数的值
                stmt.registerOutParameter(2, Types.VARCHAR); // 注册输出参数的类型

                stmt.execute(); // 执行存储过程

                String outputParam = stmt.getString(2); // 获取输出参数的值
                as_Rtn = outputParam;
                System.out.println("Output parameter: " + outputParam);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return as_Rtn;
    }

    public  String callSpSttJob(String opCode,String stt_code,String locate,String tp ){
        String as_Rtn = null;
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "{ call SP_STT_JOB(?, ?,?,?,?,?,?,?,?,?) }"; // 调用存储过程的SQL语句
            try (CallableStatement stmt = conn.prepareCall(sql)) {
                stmt.setString(1, opCode); // 设置输入参数的值
                stmt.setString(2, "0003"); // 设置输入参数的值
                stmt.setString(3, stt_code); // 设置输入参数的值
                stmt.setString(4, locate); // 设置输入参数的值
                stmt.setString(5, tp); // 设置输入参数的值
                stmt.setString(6, "OUT"); // 设置输入参数的值
                stmt.setDate(7, new java.sql.Date(new java.util.Date().getTime())); // 设置输入参数的值
                stmt.setInt(8,0);
                stmt.setString(9,"STD");
                stmt.registerOutParameter(10, Types.INTEGER); // 注册输出参数的类型

                stmt.execute(); // 执行存储过程

                String outputParam = stmt.getString(10); // 获取输出参数的值
                as_Rtn = outputParam;
                System.out.println("Output parameter: " + outputParam);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return as_Rtn;
    }

   public int callSpTemopOver(String opCode,String op_type){
       int as_Rtn = 0;
       try (Connection conn = DriverManager.getConnection(url, user, password)) {
           String sql = "{ call SP_TEMP_OP_OVER(?, ?,?) }"; // 调用存储过程的SQL语句
           try (CallableStatement stmt = conn.prepareCall(sql)) {
               System.out.println("调用spTempOP的op_code是:"+opCode);
               stmt.setString(1, opCode); // 设置输入参数的值:
               System.out.println("调用spTempOP的op_type是:"+op_type);
               stmt.setString(2, op_type);
               stmt.registerOutParameter(3, Types.INTEGER); // 注册输出参数的类型

               stmt.execute(); // 执行存储过程

               int outputParam = stmt.getInt(3); // 获取输出参数的值
               as_Rtn = outputParam;
               System.out.println("Output parameter: " + outputParam);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return as_Rtn;
   }

}
